﻿/*版权声明
 * 
 * 本 河北大学管理学院信息管理系网站 由
 * 河北大学 信管团队 开发
 * 如有任何疑问请联系开发人员
 * 
 * 项目开发者：朱胜贤、孙旭峰、水亚亚、张文元、项玥、吕芳、吴凡、谢静、李亚慧
 * 
 * 如有任何疑问请联系：河北大学信管团队
 * QQ：196396337
 * Email:zagelover@163.com
 * HomePage：http://sxgzs.com/
 * 
 * CopyRight (c) 河北大学信管团队 2010~2020
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;

/// <summary>DataAccess 为数据库操作等定义的一些类
/// 	<remark>2010-10-21 河北大学信管团队</remark>
/// </summary>
public class DataAccess
{
    #region 配置数据库连接字符串
    /// <summary>配置数据库连接字符串
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// </summary>
    private static string ConnectionString = "server=" + ConfigurationManager.AppSettings["server"] + ";database=" + ConfigurationManager.AppSettings["database"] + ";uid=" + ConfigurationManager.AppSettings["uid"] + ";pwd=" + ConfigurationManager.AppSettings["pwd"];
    #endregion 配置数据库连接字符串

    #region  执行Sql语句，返回Bool值
    /// <summary>执行Sql语句，返回Bool值
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// </summary>
    /// <param name="Sql">要执行的Sql语句</param>
    /// <param name="para">参数</param>
    /// <returns>返回BOOL值，True为执行成功</returns>
    public static bool ExecuteSQL(string sql,SqlParameter[] para)
    {
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sql, con);
        try
        {
            con.Open();
            PrepareCommand(cmd, para);
            if (cmd.ExecuteNonQuery() > 0) 
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch(Exception e)
        {
            return false;
        }
        finally
        {
            con.Close();
            con.Dispose();
            cmd.Dispose();
        }
    }
    #endregion 执行Sql语句，返回Bool值

    #region 执行Sql语句，返回SqlDataReader
    /// <summary>执行Sql语句，返回SqlDataReader 
    /// </summary>
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// <param name="sql">要执行的Sql语句</param>
    /// <param name="para">参数</param>
    /// <returns>返回SqlDataReader，需手工关闭连接</returns>
    public static SqlDataReader GetReader(string sql, SqlParameter[] para)
    {
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataReader dr = null;
        try
        {
            con.Open();
            PrepareCommand(cmd,para);
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            dr.Close();
            con.Dispose();
            cmd.Dispose();
            throw new Exception(ex.ToString());
        }
        return dr;
    }
    #endregion 执行Sql语句，返回SqlDataReader

    #region  执行Sql语句，返回DataTable
    /// <summary>执行Sql语句，返回DataTable
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// </summary>
    /// <param name="Sql">要执行的Sql语句</param>
    /// <param name="para">参数</param>
    /// <returns>返回DataTable类型的执行结果</returns>
    public static DataTable GetDataTable(string sql,SqlParameter[] para)
    {
        DataSet ds = new DataSet();
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        try
        {
            PrepareCommand(da.SelectCommand,para);
            da.Fill(ds, "tb");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            con.Close();
            con.Dispose();
            da.Dispose();
        }
        DataTable result = ds.Tables["tb"];
        return result;
    }
    #endregion 执行Sql语句，返回DataTable

    #region  执行Sql语句，返回DataSet
    /// <summary>执行Sql语句，返回DataSet
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// </summary>
    /// <param name="Sql">要执行的Sql语句</param>
    /// <param name="para">参数</param>
    /// <returns>返回DataSet类型的执行结果</returns>
    public static DataSet GetDataSet(string sql, SqlParameter[] para)
    {
        DataSet ds = new DataSet();
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        try
        {
            PrepareCommand(da.SelectCommand, para);
            da.Fill(ds, "tb");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            con.Close();
            con.Dispose();
            da.Dispose();
        }
        return ds;
    }
    #endregion 执行Sql语句，返回DataSet
    
    #region  执行Sql语句并返回受影响的行数
    /// <summary>执行Sql语句并返回受影响的行数
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// </summary>
    /// <param name="Sql">要执行的Sql语句</param>
    /// <param name="para">参数</param>
    /// <returns>返回Int类型的受影响的行数</returns>
    public static int GetCount(string sql,SqlParameter[] para)
    {
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sql, con);
        try
        {
            con.Open();
            int count;
            PrepareCommand(cmd,para);
            if (sql.Substring(0, 1) == "s" || sql.Substring(0, 1) == "S")
                count = Convert.ToInt32(cmd.ExecuteScalar());
            else
                count = cmd.ExecuteNonQuery();
            return count;
        }
        catch
        {
            return 0;
        }
        finally
        {
            con.Close();
            con.Dispose();
            cmd.Dispose();
        }
    }
    #endregion 执行Sql语句并返回受影响的行数

    #region  执行Sql语句并返回第一行第一列的值
    /// <summary>执行Sql语句并返回第一行第一列的值
    /// <remark>2010-10-21 河北大学信管团队 </remark>
    /// </summary>
    /// <param name="Sql">要执行的Sql语句</param>
    /// <param name="para">参数</param>
    /// <returns>返回第一行第一列的值</returns>
    public static String GetNo1(string sql, SqlParameter[] para)
    {
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sql, con);
        try
        {
            con.Open();
            PrepareCommand(cmd, para);
            String No1 = Convert.ToString(cmd.ExecuteScalar());
            return No1;
        }
        catch
        {
            return "false_0";
        }
        finally
        {
            con.Close();
            con.Dispose();
            cmd.Dispose();
        }
    }
    #endregion 执行Sql语句并返回第一行第一列的值

    #region  为执行命令准备参数
    /// <summary>为执行命令准备参数
    ///     <remark>2010-10-23 河北大学信管团队 </remark>
    /// </summary>
    /// <param name="cmd">SqlCommand 命令</param>
    /// <param name="cmdParms">返回带参数的命令</param>
    private static void PrepareCommand(SqlCommand cmd, SqlParameter[] cmdParms)
    {
        if (cmdParms != null)
        {
            foreach (SqlParameter parm in cmdParms)
                cmd.Parameters.Add(parm);
        }
    }
    #endregion 为执行命令准备参数
}